<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hzb.erp.api.pc.lesson.mapper.LessonStudentMapper">


    <select id="getList" resultType="com.hzb.erp.api.pc.lesson.pojo.LessonStudentVO">
        select
        t1.*
        ,t2.name, t8.mobile
        ,t3.name teacher_name
        ,t4.date, t4.start_time, t4.end_time
        ,t5.name course_name
        ,t6.name evaluate_teacher_name
        ,t7.title lesson_title
        <if test="param.studentId != null">
            , (select count(0) FROM teach_evaluation where lesson_id = t1.lesson_id and student_id = t1.student_id)
            count_teach_evaluation
            , t1.sign_state in (1,2) can_evaluate
        </if>
        from lesson_student t1
        LEFT JOIN student t2 on t2.id = t1.student_id
        LEFT JOIN staff t3 ON t3.id = t1.teacher_id
        LEFT JOIN lesson t4 ON t4.id = t1.lesson_id
        LEFT JOIN course t5 ON t5.id = t4.course_id
        LEFT JOIN staff t6 ON t6.id = t1.evaluate_teacher
        LEFT JOIN lesson t7 ON t7.id = t1.lesson_id
        LEFT JOIN user t8 ON t8.id = t2.user_id
        <where>
            <if test="param.onlyEvaluate != null and param.onlyEvaluate">
                AND t1.evaluate_time is not null
            </if>
            <if test="param.teacherId != null">
                AND t1.teacher_id = #{param.teacherId}
            </if>
            <if test="param.evaluateTeacherId != null">
                AND t1.evaluate_teacher = #{param.evaluateTeacherId}
            </if>
            <if test="param.lessonId != null">
                AND t1.lesson_id = #{param.lessonId}
            </if>
            <if test="param.courseId != null">
                AND t1.lesson_id in ( select id from lesson where course_id = #{param.courseId} )
            </if>
            <if test="param.startDate != null and param.endDate != null">
                AND ( evaluate_time &gt;= #{param.startDate} and evaluate_time &lt;= #{param.endDate} )
            </if>
            <if test="param.keyword != null and param.keyword != '' and param.keyword.length() > 1">
                AND t1.evaluation like concat('%', #{param.keyword}, '%')
            </if>
            <if test="param.studentSide == null or param.studentSide == false">
                <if test="param.studentSide == null and param.signState != null">
                    AND t1.sign_state = #{param.signState}
                </if>
                <if test="param.studentId != null">
                    AND t1.student_id = #{param.studentId}
                </if>
            </if>
            <if test="param.studentSide != null and param.studentSide">
                AND t1.student_id = #{param.studentId}
                <if test="param.signState != null and param.signState != 0">
                    AND t1.sign_state = #{param.signState}
                </if>
                <if test="param.signState == null or param.signState == 0">
                    AND t1.sign_state > 0
                </if>
            </if>

        </where>
        order by id desc
    </select>
    <select id="getConsumeCourseId" resultType="java.lang.Long">
    select IFNULL(t1.consume_course_id, t2.consume_course_id)
    from lesson_student t1
    LEFT JOIN class_student t2 on t2.class_id = t1.class_id and t2.student_id = t1.student_id and t2.deleted = 0
    where t1.lesson_id = #{lessonId} and t1.student_id = #{studentId}
    </select>
    <select id="getStudentIds" resultType="java.lang.Long">
    (select t1.student_id from lesson_student t1 where t1.lesson_id = #{id})
    union
    (select t2.student_id from class_student t2 where t2.class_id = (select class_id from lesson where id = #{id} ) and t2.deleted = 0)
    </select>

    <delete id="removeByStudentIdAndClassId">
    delete from lesson_student
    where student_id = #{studentId}
        and sign_state = 0
        and lesson_id in (
            select t2.id from lesson t2 where t2.class_id=#{classId} and state = 1
        )
    </delete>

    <select id="listFutureLessonByClassId" resultType="com.hzb.erp.api.pc.lesson.entity.Lesson">
        select * from lesson
        where class_id = #{classId}
          and (date &gt; CURDATE() or ( date = CURDATE() and start_time &gt; CURTIME()))
          and deleted = 0
    </select>
    <select id="loadSignCounts" resultType="com.hzb.erp.api.pc.lesson.pojo.LessonStudentCountsVO">
        select
            <if test="param.courseId != null">
                t1.course_id, t3.name course_name,
            </if>
            sum(case when t2.sign_state != 0 then 1 else 0 end) countTotal
            ,sum(case t2.sign_state when 1 then t2.dec_lesson_count else 0 end) countSign
            ,sum(case t2.sign_state when 2 then t2.dec_lesson_count else 0 end) countLate
            ,sum(case t2.sign_state when 3 then 1 else 0 end) countLeave
            ,sum(case t2.sign_state when 4 then 1 else 0 end) countAbsent
        from lesson t1
        LEFT JOIN lesson_student t2 on t2.lesson_id = t1.id
        <if test="param.courseId != null">
            LEFT JOIN course t3 on t1.course_id = t3.id
        </if>
        <where>
            <if test="param.studentId != null">
                AND t2.student_id =  #{param.studentId}
            </if>
            <if test="param.courseId != null">
                AND t1.course_id =  #{param.courseId}
            </if>
            <if test="param.lessonId != null">
                AND t1.id =  #{param.lessonId}
            </if>
        </where>
        <if test="param.courseId != null">
            GROUP BY t1.course_id
        </if>
    </select>
    <select id="getByLessonIdAndStudentId" resultType="com.hzb.erp.api.pc.lesson.entity.LessonStudent">
        select * from lesson_student where lesson_id = #{lessonId} and student_id = #{studentId} limit 1
    </select>

    <update id="updateCourseIdByClassStudentForFuture" parameterType="com.hzb.erp.api.pc.clazz.entity.ClassStudent">
        update lesson_student ls
        left join lesson l on l.id = ls.lesson_id
        set ls.consume_course_id = #{consumeCourseId}
        where ls.student_id = #{studentId}
          and ls.sign_state = 0
          and ls.class_id = #{classId}
          and l.date &gt;= CURDATE()
    </update>

</mapper>
